package com.itheima.service;

import cn.afterturn.easypoi.csv.CsvExportUtil;
import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.word.WordExportUtil;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.itheima.mapper.ResourceMapper;
import com.itheima.mapper.UserMapper;
import com.itheima.pojo.Resource;
import com.itheima.pojo.User;
import com.itheima.utils.EntityUtils;
import com.itheima.utils.ExcelExportEngine;
import com.opencsv.CSVWriter;
import com.zaxxer.hikari.HikariDataSource;
import net.sf.jasperreports.engine.JREmptyDataSource;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ResourceUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;
    @Autowired
    private ResourceMapper resourceMapper;
    @Autowired
    private HikariDataSource hikariDataSource;

    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-ss");

    public List<User> findAll() {
        return userMapper.selectAll();
    }

    public List<User> findPage(Integer page, Integer pageSize) {
        PageHelper.startPage(page, pageSize);  //开启分页
        Page<User> userPage = (Page<User>) userMapper.selectAll(); //实现查询
        return userPage.getResult();
    }

    /*public void downLoadXlsByJxl(HttpServletResponse response) {
        try {
            //创建一个工作簿
            OutputStream os = response.getOutputStream();
            WritableWorkbook workbook = Workbook.createWorkbook(os);
            //创建一个工作表
            WritableSheet sheet = workbook.createSheet("一个JXL入门", 0);
            //设置列宽：第一个参数：列的索引值；第二个参数：1代表一个标准字母的宽度
            sheet.setColumnView(0, 5);
            sheet.setColumnView(1, 8);
            sheet.setColumnView(2, 15);
            sheet.setColumnView(3, 15);
            sheet.setColumnView(4, 30);

            //处理标题
            String[] titles = {"编号", "姓名", "手机号", "入职日期", "现住址"};
            Label label = null;
            for (int i = 0; i < titles.length; i++) {
                //格式：列角标，行角标，单元格中的内容
                label = new Label(i, 0, titles[i]);
                sheet.addCell(label);
            }

            //处理导出的内容
            List<User> userList = this.findAll();
            int rowIndex = 1;
            for (User user : userList) {
                label = new Label(0, rowIndex, user.getId().toString());
                sheet.addCell(label);
                label = new Label(1, rowIndex, user.getUserName());
                sheet.addCell(label);
                label = new Label(2, rowIndex, user.getPhone());
                sheet.addCell(label);
                label = new Label(3, rowIndex, simpleDateFormat.format(user.getHireDate()));
                sheet.addCell(label);
                label = new Label(4, rowIndex, user.getAddress());
                sheet.addCell(label);

                rowIndex++;
            }

            //导出文件的名称
            String filename = "一个JXL入门.xls";
            //设置文件的打开方式和mine类型
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), StandardCharsets.ISO_8859_1));
            response.setContentType("application/vnd.ms-excel");

            //导出
            workbook.write();
            //关闭资源
            workbook.close();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }*/

    public void uploadExcel(MultipartFile file) throws Exception {
        //有内容的workBook工作簿
        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        //获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        //当前工作表的最后一行的索引值
        int lastRowIndex = sheet.getLastRowNum();
        Row row = null;
        User user = null;

        for (int i = 1; i <= lastRowIndex; i++) {
            row = sheet.getRow(i);
            //用户名
            String userName = row.getCell(0).getStringCellValue();
            //手机号
            String phone = null;
            try {
                phone = row.getCell(1).getStringCellValue();
            } catch (Exception e) {
                phone = row.getCell(1).getNumericCellValue() + "";
            }
            //省份
            String province = row.getCell(2).getStringCellValue();
            //城市
            String city = row.getCell(3).getStringCellValue();
            //工资
            Integer salary = ((Double) row.getCell(4).getNumericCellValue()).intValue();
            //入职日期
            Date hireDate = sdf.parse(row.getCell(5).getStringCellValue());
            //出生日期
            Date birthday = sdf.parse(row.getCell(6).getStringCellValue());
            //现住地址
            String address = row.getCell(7).getStringCellValue();

            user = new User();
            user.setUserName(userName);
            user.setPhone(phone);
            user.setProvince(province);
            user.setCity(city);
            user.setSalary(salary);
            user.setHireDate(hireDate);
            user.setBirthday(birthday);
            user.setAddress(address);

            userMapper.insert(user);
        }
    }

    public void downLoadXlsxByPoi(HttpServletResponse response) {
        try {
            //创建一个工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();
            //创建一个工作表
            Sheet sheet = workbook.createSheet("用户数据");
            //设置列宽：第一个参数：列的索引值；第二个参数：1代表一个标准字母宽度的1/256
            sheet.setColumnWidth(0, 5 * 256);
            sheet.setColumnWidth(1, 8 * 256);
            sheet.setColumnWidth(2, 15 * 256);
            sheet.setColumnWidth(3, 15 * 256);
            sheet.setColumnWidth(4, 30 * 256);

            //处理标题
            String[] titles = {"编号", "姓名", "手机号", "入职日期", "现住址"};
            Row row = sheet.createRow(0);
            Cell cell = null;
            for (int i = 0; i < titles.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(titles[i]);
            }

            //处理导出的内容
            List<User> userList = this.findAll();
            int rowIndex = 1;
            for (User user : userList) {
                row = sheet.createRow(rowIndex);

                cell = row.createCell(0);
                cell.setCellValue(user.getId().toString());
                cell = row.createCell(1);
                cell.setCellValue(user.getUserName());
                cell = row.createCell(2);
                cell.setCellValue(user.getPhone());
                cell = row.createCell(3);
                cell.setCellValue(sdf.format(user.getHireDate()));
                cell = row.createCell(4);
                cell.setCellValue(user.getAddress());

                rowIndex++;
            }

            //导出文件的名称
            String filename = "员工数据.xlsx";
            //设置文件的打开方式和mine类型
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), StandardCharsets.ISO_8859_1));
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

            //导出
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void downLoadXlsxByPoiWithCellStyle(HttpServletResponse response) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("有样式的数据");
        //设置列宽：第一个参数：列的索引值；第二个参数：1代表一个标准字母宽度的1/256
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 8 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 15 * 256);
        sheet.setColumnWidth(4, 30 * 256);
        Cell cell = null;

        //大标题行
        Row bigTitleRow = sheet.createRow(0);
        //行高
        bigTitleRow.setHeightInPoints(42);
        CellStyle bigTitleRowCellStyle = workbook.createCellStyle();
        //边框：下，左，右，上
        bigTitleRowCellStyle.setBorderBottom(BorderStyle.THIN);
        bigTitleRowCellStyle.setBorderLeft(BorderStyle.THIN);
        bigTitleRowCellStyle.setBorderRight(BorderStyle.THIN);
        bigTitleRowCellStyle.setBorderTop(BorderStyle.THIN);
        //对齐方式：水平对齐、垂直对齐
        bigTitleRowCellStyle.setAlignment(HorizontalAlignment.CENTER);
        bigTitleRowCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //字体：黑体，字号18
        Font bigFont = workbook.createFont();
        bigFont.setFontName("黑体");
        bigFont.setFontHeightInPoints((short) 18);
        bigTitleRowCellStyle.setFont(bigFont);
        for (int i = 0; i < 5; i++) {
            cell = bigTitleRow.createCell(i);
            cell.setCellStyle(bigTitleRowCellStyle);
        }
        //合并单元格：int firstRow起始行, int lastRow结束行, int firstCol起始列, int lastCol结束列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
        sheet.getRow(0).getCell(0).setCellValue("用户信息数据");

        //小标题行
        Row littleTitleRow = sheet.createRow(1);
        //行高
        littleTitleRow.setHeightInPoints(32);
        CellStyle littleTitleRowCellStyle = workbook.createCellStyle();
        //样式克隆
        littleTitleRowCellStyle.cloneStyleFrom(bigTitleRowCellStyle);
        //字体：黑体，字号12，加粗
        Font littleFont = workbook.createFont();
        littleFont.setFontName("宋体");
        littleFont.setFontHeightInPoints((short) 12);
        littleFont.setBold(true);
        littleTitleRowCellStyle.setFont(littleFont);
        String[] titles = {"编号", "姓名", "手机号", "入职日期", "现住址"};
        for (int i = 0; i < titles.length; i++) {
            cell = littleTitleRow.createCell(i);
            cell.setCellStyle(littleTitleRowCellStyle);
            cell.setCellValue(titles[i]);
        }

        //内容行
        Row contentTitleRow = null;
        CellStyle contentTitleRowCellStyle = workbook.createCellStyle();
        //样式克隆
        contentTitleRowCellStyle.cloneStyleFrom(bigTitleRowCellStyle);
        contentTitleRowCellStyle.setAlignment(HorizontalAlignment.LEFT);
        //字体：黑体，字号12
        Font contentFont = workbook.createFont();
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 11);
        contentFont.setBold(false);
        contentTitleRowCellStyle.setFont(contentFont);
        //处理导出的内容
        List<User> userList = this.findAll();
        int rowIndex = 2;
        for (User user : userList) {
            contentTitleRow = sheet.createRow(rowIndex);

            cell = contentTitleRow.createCell(0);
            cell.setCellStyle(contentTitleRowCellStyle);
            cell.setCellValue(user.getId().toString());
            cell = contentTitleRow.createCell(1);
            cell.setCellStyle(contentTitleRowCellStyle);
            cell.setCellValue(user.getUserName());
            cell = contentTitleRow.createCell(2);
            cell.setCellStyle(contentTitleRowCellStyle);
            cell.setCellValue(user.getPhone());
            cell = contentTitleRow.createCell(3);
            cell.setCellStyle(contentTitleRowCellStyle);
            cell.setCellValue(sdf.format(user.getHireDate()));
            cell = contentTitleRow.createCell(4);
            cell.setCellStyle(contentTitleRowCellStyle);
            cell.setCellValue(user.getAddress());

            rowIndex++;
        }

        //导出文件的名称
        String filename = "员工数据.xlsx";
        //设置文件的打开方式和mine类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), StandardCharsets.ISO_8859_1));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        //导出
        try {
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void downLoadXlsxByPoiWithTemplate(HttpServletRequest request, HttpServletResponse response) throws Exception {
        //获取模板的路径：springboot项目获取根目录
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootPath.getAbsolutePath(), "/excel_template/userList.xlsx");
        //读取模板文件产生workbook对象，这个workbook是一个有内容的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(templateFile);
        //读取工作簿的第一个工作表，向工作表中放数据
        XSSFSheet sheet = workbook.getSheetAt(0);
        //获取第二个表的sheet中那个单元格中的单元格样式
        XSSFCellStyle cellStyle = workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle();
        List<User> userList = this.findAll();
        int rowIndex = 2;
        Row row = null;
        Cell cell = null;
        for (User user : userList) {
            row = sheet.createRow(rowIndex);
            row.setHeightInPoints(15);
            cell = row.createCell(0);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(user.getId().toString());
            cell = row.createCell(1);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(user.getUserName());
            cell = row.createCell(2);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(user.getPhone());
            cell = row.createCell(3);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(sdf.format(user.getHireDate()));
            cell = row.createCell(4);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(user.getAddress());

            rowIndex++;
        }

        //将第二个工作表删除
        workbook.removeSheetAt(1);

        //导出文件的名称
        String filename = "员工数据.xlsx";
        //设置文件的打开方式和mine类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), StandardCharsets.ISO_8859_1));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        //导出
        workbook.write(response.getOutputStream());
    }

    public void downLoadUserInfoWithTemplate(Long id, HttpServletRequest request, HttpServletResponse response) throws Exception {
        //获取模板的路径：springboot项目获取根目录
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootPath.getAbsolutePath(), "/excel_template/userInfo.xlsx");
        //读取模板文件产生workbook对象，这个workbook是一个有内容的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(templateFile);
        //读取工作簿的第一个工作表，向工作表中放数据
        XSSFSheet sheet = workbook.getSheetAt(0);
        User user = userMapper.selectByPrimaryKey(id);

        //向模板中单元格放数据
        //用户名  第2行第2列
        sheet.getRow(1).getCell(1).setCellValue(user.getUserName());
        //手机号  第3行第2列
        sheet.getRow(2).getCell(1).setCellValue(user.getPhone());
        //生日  第4行第2列
        sheet.getRow(3).getCell(1).setCellValue(sdf.format(user.getBirthday()));
        //工资  第5行第2列
        sheet.getRow(4).getCell(1).setCellValue(user.getSalary());
        //入职日期  第6行第2列
        sheet.getRow(5).getCell(1).setCellValue(sdf.format(user.getHireDate()));
        //省份  第7行第2列
        sheet.getRow(6).getCell(1).setCellValue(user.getProvince());
        //现住址  第8行第2列
        sheet.getRow(7).getCell(1).setCellValue(user.getAddress());
        //照片 第2行第3列 - 第5行第4列
        //先创建一个字节输出流
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        // BufferedImage 是一个带缓冲区图像类，主要作用是将一副图片加载到内存中
        BufferedImage bufferImg = ImageIO.read(new File(rootPath, user.getPhoto()));
        //将读取到图像放入到输出流中
        String extName = user.getPhoto().substring(user.getPhoto().lastIndexOf(".") + 1).toUpperCase();
        ImageIO.write(bufferImg, extName, byteArrayOut);
        //创建一个绘图控制类，负责画图
        Drawing patriarch = sheet.createDrawingPatriarch();

        //指定把图片放到哪个位置
        /*
         *   dx1：定义了图片在第一个cell内的偏移x坐标，既左上角所在cell的偏移x坐标，一般可设0
         *   dy1：定义了图片在第一个cell内的偏移y坐标，既左上角所在cell的偏移y坐标，一般可设0
         *   dx2：定义了图片在第二个cell内的偏移x坐标，既右下角所在cell的偏移x坐标，一般可设0
         *   dy2：定义了图片在第二个cell内的偏移y坐标，既右下角所在cell的偏移y坐标，一般可设0
         *   col1：第一个cell所在列，既图片左上角所在列
         *   row1：第一个cell所在行，既图片左上角所在行
         *   col2：第二个cell所在列，既图片右下角所在列
         *   row2：第二个cell所在行，既图片右下角所在行
         * */
        ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 2, 1, 4, 5);

        int format = 0;
        switch (extName) {
            case "JPG":
                format = XSSFWorkbook.PICTURE_TYPE_JPEG;
                break;
            case "JPEG":
                format = XSSFWorkbook.PICTURE_TYPE_JPEG;
                break;
            case "PNG":
                format = XSSFWorkbook.PICTURE_TYPE_PNG;
        }
        //把图片写入到sheet指定的位置
        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), format));

        //司龄  第6行第4列  -- 建议在Excel模板中设置好公式，这样打开直接导出的excel文档时公式会直接运行出结果
        //sheet.getRow(5).getCell(3).setCellFormula("DATEDIF(B6,TODAY(),\"y\")");
        //城市  第7行第4列
        sheet.getRow(6).getCell(3).setCellValue(user.getCity());

        //导出的文件名称
        String filename = "用户(" + user.getUserName() + ")详细信息数据.xlsx";
        //设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
    }

    public void downLoadUserInfoWithTemplate2(Long id, HttpServletRequest request, HttpServletResponse response) throws Exception {
        //获取模板的路径：springboot项目获取根目录
        File rootFile = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootFile.getAbsolutePath(), "/excel_template/userInfo2.xlsx");
        //读取模板文件产生workbook对象，这个workbook是一个有内容的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(templateFile);
        User user = userMapper.selectByPrimaryKey(id);
        //通过自定义的引擎放入数据
        ExcelExportEngine.writeToExcel(user, workbook, rootFile.getAbsolutePath() + user.getPhoto());

        //导出的文件名称
        String filename = "员工(" + user.getUserName() + ")详细信息数据.xlsx";
        //设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
    }

    /**
     * 百万数据的导出：1. 使用高版本的excel 2. 使用sax方式解析Excel（xml）
     * 限制：1. 不能使用模板  2. 不能使用太多的样式
     * 用户模式：加载并读取Excel时，是通过一次性的将所有数据加载到内存中再去解析每个单元格内容，当Excel数据量较大时，由于不同的运行环境可能会造成内存不足甚至OOM异常。
     * 事件模式：他逐行扫描文档，一边扫描一边解析。由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势。
     *
     * @param response
     */
    public void downLoadMillion(HttpServletResponse response) throws Exception {
        //指定使用的是sax方式解析：逐行解析
        Workbook workbook = new SXSSFWorkbook();
        /*
            dom4j解析方式
            Workbook workbook = new XSSFWorkbook();
        */
        //导出500W条数据 不可能放到同一个sheet中， 规定：每个sheet不能超过100W条数据
        int page = 1;
        //记录了处理数据的个数
        int num = 0;
        //记录的是每个sheet的行索引
        int rowIndex = 1;
        Row row = null;
        Sheet sheet = null;
        while (true) {
            List<User> userList = this.findPage(page, 100000);
            if (CollectionUtils.isEmpty(userList)) {
                //用户数据为空，结束
                break;
            }
            if (num % 1000000 == 0) {
                sheet = workbook.createSheet("第" + ((num / 1000000) + 1) + "个工作表");
                //每个sheet中的行索引重置
                rowIndex = 1;
                //设置小标题
                String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
                Row titleRow = sheet.createRow(0);
                for (int i = 0; i < 5; i++) {
                    titleRow.createCell(i).setCellValue(titles[i]);
                }
            }
            for (User user : userList) {
                row = sheet.createRow(rowIndex);
                row.createCell(0).setCellValue(user.getId());
                row.createCell(1).setCellValue(user.getUserName());
                row.createCell(2).setCellValue(user.getPhone());
                row.createCell(3).setCellValue(simpleDateFormat.format(user.getHireDate()));
                row.createCell(4).setCellValue(user.getAddress());

                rowIndex++;
                num++;
            }
            //当前页码加1
            page++;
        }
        String filename = "百万用户数据的导出.xlsx";
        response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(response.getOutputStream());
    }

    /**
     * 导出用户数据到CSV文件中
     * CSV文件：Comma-Separated Values（逗号分隔值或者字符分割值），其文件以纯文本的形式存储表格数据。该文件是一个字符序列，可以由任意数目的记录组成，
     * 记录间以某种换行符分割。每条记录由字段组成，字段间的分隔符是其他字符或字符串。所有的记录都有完全相同的字段序列，相当于一个结构化表的纯文本形式。
     *
     * @param response
     */
    public void downLoadCSV(HttpServletResponse response) throws Exception {
        ServletOutputStream outputStream = response.getOutputStream();
        String fileName = "百万用户数据的导出.csv";
        response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));
        response.setContentType("text/csv");
        CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(outputStream, "utf-8"));
        //写入了小标题数据
        String[] titles = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
        csvWriter.writeNext(titles);

        int page = 1;
        while (true) {
            List<User> userList = this.findPage(page, 200000);
            if (CollectionUtils.isEmpty(userList)) {
                break;
            }
            for (User user : userList) {
                csvWriter.writeNext(new String[]{user.getId().toString(), user.getUserName(), user.getPhone(),
                        simpleDateFormat.format(user.getHireDate()), user.getAddress()});
            }
            page++;
            csvWriter.flush();
        }
        csvWriter.close();


    }

    public User findById(Long id) {
        //查询用户
        User user = userMapper.selectByPrimaryKey(id);
        //根据用户id查询办公用品
        Resource resource = new Resource();
        resource.setUserId(id);
        List<Resource> resourceList = resourceMapper.select(resource);
        user.setResourceList(resourceList);
        return user;
    }

    /**
     * 下载用户的合同文档
     *
     * @param id
     * @param response
     */
    public void downloadContract(Long id, HttpServletResponse response) throws Exception {
        //获取模板的路径：springboot项目获取根目录
        File rootFile = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootFile.getAbsolutePath(), "/word_template/contract_template.docx");
        XWPFDocument word = new XWPFDocument(new FileInputStream(templateFile));

        //查询当前用户user
        User user = this.findById(id);
        Map<String, String> params = new HashMap<>();
        params.put("userName", user.getUserName());
        params.put("hireDate", sdf.format(user.getHireDate()));
        params.put("address", user.getAddress());

        //替换数据
        //处理正文
        List<XWPFParagraph> paragraphs = word.getParagraphs();
        for (XWPFParagraph paragraph : paragraphs) {
            List<XWPFRun> runs = paragraph.getRuns();
            for (XWPFRun run : runs) {
                String text = run.getText(0);
                for (String key : params.keySet()) {
                    if (text.contains(key)) {
                        run.setText(text.replaceAll(key, params.get(key)), 0);
                    }
                }
            }
        }
        //处理表格
        List<Resource> resourceList = user.getResourceList();
        XWPFTable xwpfTable = word.getTables().get(0);

        XWPFTableRow row = xwpfTable.getRow(0);
        int rowIndex = 1;
        for (Resource resource : resourceList) {
            //添加行
            copyRow(xwpfTable, row, rowIndex);
            XWPFTableRow row1 = xwpfTable.getRow(rowIndex);
            row1.getCell(0).setText(resource.getName());
            row1.getCell(1).setText(resource.getPrice().toString());
            row1.getCell(2).setText(resource.getNeedReturn() ? "需求" : "不需要");

            File imageFile = new File(rootFile, "/static" + resource.getPhoto());
            setCellImage(row1.getCell(3), imageFile);
            rowIndex++;
        }

        //导出word
        String filename = "员工(" + user.getUserName() + ")合同.docx";
        response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        word.write(response.getOutputStream());
    }


    /**
     * 向单元格中写入图片
     *
     * @param cell
     * @param imageFile
     */
    private void setCellImage(XWPFTableCell cell, File imageFile) {
        XWPFRun run = cell.getParagraphs().get(0).createRun();
        try (FileInputStream inputStream = new FileInputStream(imageFile)) {
            run.addPicture(inputStream, XWPFDocument.PICTURE_TYPE_JPEG, imageFile.getName(),
                    Units.toEMU(100), Units.toEMU(50));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 用于深克隆行
     *
     * @param xwpfTable
     * @param sourceRow
     * @param rowIndex
     */
    private void copyRow(XWPFTable xwpfTable, XWPFTableRow sourceRow, int rowIndex) {
        XWPFTableRow targetRow = xwpfTable.insertNewTableRow(rowIndex);
        targetRow.getCtRow().setTrPr(sourceRow.getCtRow().getTrPr());
        //获取源行的单元格
        List<XWPFTableCell> cells = sourceRow.getTableCells();
        if (CollectionUtils.isEmpty(cells)) {
            return;
        }
        XWPFTableCell targetCell = null;
        for (XWPFTableCell cell : cells) {
            targetCell = targetRow.addNewTableCell();
            //附上单元格的样式，单元格的属性
            targetCell.getCTTc().setTcPr(cell.getCTTc().getTcPr());
            targetCell.getParagraphs().get(0).getCTP().setPPr(cell.getParagraphs().get(0).getCTP().getPPr());
        }
    }

    public void downLoadWithEasyPOI(Long id, HttpServletResponse response) throws Exception {
        List<User> userList = userMapper.selectAll();
        //指定导出的格式是高版本的方法
        ExportParams exportParams = new ExportParams("员工信息", "数据", ExcelType.XSSF);
        //使用EasyPOI提供的方法
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, userList);

        String filename = "员工信息.xlsx";
        response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(response.getOutputStream());
    }

    public void uploadExcelWithEasyPOI(MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        //有多少行的标题
        importParams.setTitleRows(1);
        //有多少行的头
        importParams.setHeadRows(1);
        List<User> userList = ExcelImportUtil.importExcel(file.getInputStream(), User.class, importParams);
        System.out.println(userList);
        for (User user : userList) {
            user.setId(null);
            userMapper.insertSelective(user);
        }
    }

    public void downLoadUserInfoWithEastPOI(Long id, HttpServletResponse response, HttpServletResponse response1) throws Exception {
        //获取模板的路径：springboot项目获取根目录
        File rootFile = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootFile.getAbsolutePath(), "/excel_template/userInfo3.xlsx");

        //读取模板文件
        TemplateExportParams params = new TemplateExportParams(templateFile.getPath(), true);

        User user = userMapper.selectByPrimaryKey(id);
        Map<String, Object> map = EntityUtils.entityToMap(user);
        ImageEntity image = new ImageEntity();
        //向下合并三行
        image.setRowspan(4);
        //向右合并两列
        image.setColspan(2);
        image.setUrl(user.getPhoto());
        map.put("photo", image);

        Workbook workbook = ExcelExportUtil.exportExcel(params, map);

        //导出的文件名称
        String filename = "员工(" + user.getUserName() + ")详细信息数据.xlsx";
        //设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
    }

    public void downLoadCSVWithEasyPOI(HttpServletResponse response) throws Exception {
        ServletOutputStream outputStream = response.getOutputStream();
        String fileName = "百万数据.csv";
        response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));
        response.setContentType("application/csv");
        //创建一个用来写入到csv文件中的writer
        CsvExportParams params = new CsvExportParams();
        //设置忽略的列，写表头 中文
        params.setExclusions(new String[]{"照片"});
        List<User> userList = userMapper.selectAll();
        CsvExportUtil.exportCsv(params, User.class, userList, outputStream);
    }

    public void downloadContractWithEasyPOI(Long id, HttpServletResponse response) throws Exception {
        //获取模板的路径：springboot项目获取根目录
        File rootFile = new File(ResourceUtils.getURL("classpath:").getPath());
        File templateFile = new File(rootFile.getAbsolutePath(), "/word_template/contract_template2.docx");
        User user = this.findById(id);
        //把需要的数据放到map中，方便替换
        HashMap<String, Object> params = new HashMap<>();
        params.put("userName", user.getUserName());
        params.put("hireDate", sdf.format(user.getHireDate()));
        params.put("address", user.getAddress());
        ImageEntity imageEntityContent = new ImageEntity();
        imageEntityContent.setUrl(rootFile.getPath() + user.getPhoto());
        imageEntityContent.setWidth(100);
        imageEntityContent.setHeight(50);
        params.put("photo", imageEntityContent);

        //表格中需要的数据
        List<Map> mapList = new ArrayList<>();
        Map<String, Object> map = null;
        for (Resource resource : user.getResourceList()) {
            map = new HashMap<>();
            map.put("name", resource.getName());
            map.put("price", resource.getPrice());
            map.put("needReturn", resource.getNeedReturn());
            ImageEntity imageEntity = new ImageEntity();
            imageEntity.setHeight(180);
            imageEntity.setWidth(240);
            imageEntity.setUrl(rootFile.getPath() + "/static/" + resource.getPhoto());
            map.put("photo", imageEntity);
            mapList.add(map);
        }
        params.put("maplist", mapList);

        //模板文档结合数据
        XWPFDocument xwpfDocument = WordExportUtil.exportWord07(templateFile.getPath(), params);
        //导出word
        String filename = "员工(" + user.getUserName() + ")合同.docx";
        response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        xwpfDocument.write(response.getOutputStream());
    }

    public void downLoadPDFByDB(HttpServletResponse response) throws Exception {
        //获取模板文件
        String ctxPath = ResourceUtils.getURL("classpath:").getPath();
        String filePath = ctxPath + "/pdf_template/userList_db.jasper";
        ServletOutputStream os = response.getOutputStream();
        String fileName = "用户列表数据.pdf";
        //设置两个头 一个是文件的打开方式 一个是mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));
        response.setContentType("application/pdf");
        FileInputStream is = new FileInputStream(filePath);
        //创建JasperPrint,向jasper文件中填充数据
        Map parameters = new HashMap<>();
        JasperPrint jasperPrint = JasperFillManager.fillReport(is, parameters, hikariDataSource.getConnection());
        JasperExportManager.exportReportToPdfStream(jasperPrint, os);
    }

    public void downLoadPDF(HttpServletResponse response) throws Exception {
        //获取模板文件
        String ctxPath = ResourceUtils.getURL("classpath:").getPath();
        String filePath = ctxPath + "/pdf_template/userList.jasper";
        ServletOutputStream os = response.getOutputStream();
        String fileName = "用户列表数据.pdf";
        //设置两个头 一个是文件的打开方式 一个是mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));
        response.setContentType("application/pdf");
        FileInputStream is = new FileInputStream(filePath);
        //创建JasperPrint,向jasper文件中填充数据
        Map parameters = new HashMap<>();
        List<User> userList = userMapper.selectAll();
        userList = userList.stream().map(user -> {
            if (!StringUtils.isEmpty(user.getHireDate())) {
                user.setHireDateStr(sdf.format(user.getHireDate()));
            }
            return user;
        }).collect(Collectors.toList());
        JRBeanCollectionDataSource dataSource = new JRBeanCollectionDataSource(userList);


        JasperPrint jasperPrint = JasperFillManager.fillReport(is, parameters, dataSource);
        JasperExportManager.exportReportToPdfStream(jasperPrint, os);
    }

    public void downLoadUserInfoPDF(Long id, HttpServletResponse response) throws Exception {
        //获取模板文件
        String ctxPath = ResourceUtils.getURL("classpath:").getPath();
        String filePath = ctxPath + "/pdf_template/userInfo.jasper";
        ServletOutputStream os = response.getOutputStream();
        String fileName = "用户详细数据.pdf";
        //设置两个头 一个是文件的打开方式 一个是mime类型
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));
        response.setContentType("application/pdf");
        FileInputStream is = new FileInputStream(filePath);
        User user = userMapper.selectByPrimaryKey(id);
        Map<String, Object> parameters = EntityUtils.entityToMap(user);
        //因为模板中的salary字段是字符串
        parameters.put("salary", user.getSalary().toString());
        parameters.put("photo", ctxPath + user.getPhoto());

        JasperPrint jasperPrint = JasperFillManager.fillReport(is, parameters, new JREmptyDataSource());
        JasperExportManager.exportReportToPdfStream(jasperPrint, os);
    }
}
